﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;



using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.GCGBPlasterDTSTableAdapters;


namespace BusinessLogicLayer
{
    public class GCGBPlasterBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.GCGBPlaster; } }

        private UserInformation userOrganization;

        private GCGBPlasterMaster gcgbPlasterMaster;

        private MyBindingList<GCGBPlasterCollection> gcgbPlasterCollectionList;

        private bool ReadonlyMode { get; set; }

        public GCGBPlasterBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                gcgbPlasterMaster = new GCGBPlasterMaster();

                this.gcgbPlasterCollectionList = new MyBindingList<GCGBPlasterCollection>();

                GlobalDefaultValue.Apply(this);


                this.GCGBPlasterMaster.PropertyChanged += new PropertyChangedEventHandler(GCGBPlasterMaster_PropertyChanged);
                this.gcgbPlasterCollectionList.ListChanged += new ListChangedEventHandler(GCGBPlasterCollection_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void GCGBPlasterMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            if (!this.ReadonlyMode && (e.PropertyName == "GCGBCommandID" || e.PropertyName == "IsDraft"))
            {
                this.StopTracking();
                this.GCGBPlasterGetDetail();
                this.StartTracking();
            }

            this.SetDirty();
        }

        private void GCGBPlasterCollection_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private GCGBPlasterListingTableAdapter listingTableAdapter;
        protected GCGBPlasterListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new GCGBPlasterListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private GCGBPlasterMasterTableAdapter masterTableAdapter;
        protected GCGBPlasterMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new GCGBPlasterMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        GCGBPlasterCollectionTableAdapter collectionTableAdapter;
        protected GCGBPlasterCollectionTableAdapter CollectionTableAdapter
        {
            get
            {
                if (collectionTableAdapter == null) collectionTableAdapter = new GCGBPlasterCollectionTableAdapter();
                return collectionTableAdapter;
            }
        }


        GCGBPlasterGetDetailTableAdapter getDetailTableAdapter;
        protected GCGBPlasterGetDetailTableAdapter GetDetailTableAdapter
        {
            get
            {
                if (getDetailTableAdapter == null) getDetailTableAdapter = new GCGBPlasterGetDetailTableAdapter();
                return getDetailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public GCGBPlasterDTS.GCGBPlasterListingDataTable GCGBPlasterListing(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public GCGBPlasterMaster GCGBPlasterMaster
        {
            get { return this.gcgbPlasterMaster; }
        }

        public MyBindingList<GCGBPlasterCollection> GCGBPlasterCollectionList
        {
            get { return this.gcgbPlasterCollectionList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int GCGBPlasterID   //Primary Key
        {
            get { return this.GCGBPlasterMaster.GCGBPlasterID; }
            private set
            {
                if (this.GCGBPlasterMaster.GCGBPlasterID != value)
                {
                    this.StopTracking();

                    this.GCGBPlasterMaster.GCGBPlasterID = value;

                    this.GCGBPlasterGetMaster();
                    this.GCGBPlasterGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void GCGBPlasterGetMaster()
        {
            if (this.GCGBPlasterID > 0)
            {
                GCGBPlasterDTS.GCGBPlasterMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.GCGBPlasterID);

                if (masterDataTable.Count > 0)
                {
                    this.GCGBPlasterMaster.StopTracking();

                    this.GCGBPlasterMaster.GCGBCommandID = masterDataTable[0].GCGBCommandID;
                    this.GCGBPlasterMaster.GCGBCommandReference = masterDataTable[0].GCGBCommandReference;
                    this.GCGBPlasterMaster.GCGBCommandDate = masterDataTable[0].GCGBCommandDate;

                    this.GCGBPlasterMaster.GCGBPlasterDate = masterDataTable[0].GCGBPlasterDate;
                    this.GCGBPlasterMaster.EntryDate = masterDataTable[0].EntryDate;

                    this.GCGBPlasterMaster.GCGBPlasterReference = masterDataTable[0].GCGBPlasterReference;
                    this.GCGBPlasterMaster.Description = masterDataTable[0].Description;

                    this.GCGBPlasterMaster.PreparedEmployeeID = masterDataTable[0].PreparedEmployeeID;
                    this.GCGBPlasterMaster.ApprovedEmployeeID = masterDataTable[0].ApprovedEmployeeID;

                    this.GCGBPlasterMaster.Remarks = masterDataTable[0].Remarks;

                    this.GCGBPlasterMaster.IsDraft = masterDataTable[0].IsDraft;

                    this.GCGBPlasterMaster.StartTracking();

                    this.GCGBPlasterMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.GCGBPlasterMaster);
                this.GCGBPlasterMaster.GCGBPlasterDate = DateTime.Today;
                this.GCGBPlasterMaster.EntryDate = DateTime.Today;
                this.GCGBPlasterMaster.Reset();
            }
        }


        private void GCGBPlasterGetDetail()
        {
            this.gcgbPlasterCollectionList.RaiseListChangedEvents = false;
            this.gcgbPlasterCollectionList.Clear();
            if (this.GCGBPlasterID >= 0)
            {
                if (this.GCGBPlasterMaster.GCGBCommandID > 0)
                {                    
                    GCGBPlasterDTS.GCGBPlasterGetDetailDataTable gcgbPlasterGetDetailDataTable = this.GetDetailTableAdapter.GetData(this.GCGBPlasterID, this.GCGBPlasterMaster.GCGBCommandID, this.ReadonlyMode, this.GCGBPlasterMaster.IsDraft);

                    if (gcgbPlasterGetDetailDataTable.Rows.Count > 0)
                    {
                        int gcgbCommandID = 0; int gcgbCommandSerialID = 0;
                        foreach (GCGBPlasterDTS.GCGBPlasterGetDetailRow gcgbPlasterGetDetailRow in gcgbPlasterGetDetailDataTable.Rows)
                        {
                            if (gcgbPlasterGetDetailRow.RSC == 0 || gcgbCommandID != gcgbPlasterGetDetailRow.GCGBCommandID || gcgbCommandSerialID != gcgbPlasterGetDetailRow.GCGBCommandSerialID )
                            {
                                gcgbCommandID = gcgbPlasterGetDetailRow.GCGBCommandID; gcgbCommandSerialID = gcgbPlasterGetDetailRow.GCGBCommandSerialID;
                                this.gcgbPlasterCollectionList.Add(new GCGBPlasterCollection(gcgbPlasterGetDetailRow.GCGBCommandID, gcgbPlasterGetDetailRow.GCGBCommandSerialID, gcgbPlasterGetDetailRow.GCGBCommandDate, gcgbPlasterGetDetailRow.WorkerNameID, gcgbPlasterGetDetailRow.MaterialSetID, gcgbPlasterGetDetailRow.RemainsQuantity, gcgbPlasterGetDetailRow.Quantity, gcgbPlasterGetDetailRow.QuantityLoss, 
                                                                                             gcgbPlasterGetDetailRow.MaterialSemiID1, gcgbPlasterGetDetailRow.QuantityPerSet1, gcgbPlasterGetDetailRow.QuantitySemiBad1, gcgbPlasterGetDetailRow.QuantitySemiLoss1, gcgbPlasterGetDetailRow.QuantitySemiPreviousMinus1,
                                                                                             gcgbPlasterGetDetailRow.MaterialSemiID2, gcgbPlasterGetDetailRow.QuantityPerSet2, gcgbPlasterGetDetailRow.QuantitySemiBad2, gcgbPlasterGetDetailRow.QuantitySemiLoss2, gcgbPlasterGetDetailRow.QuantitySemiPreviousMinus2, 
                                                                                             gcgbPlasterGetDetailRow.MaterialSemiID3, gcgbPlasterGetDetailRow.QuantityPerSet3, gcgbPlasterGetDetailRow.QuantitySemiBad3, gcgbPlasterGetDetailRow.QuantitySemiLoss3, gcgbPlasterGetDetailRow.QuantitySemiPreviousMinus3, gcgbPlasterGetDetailRow.Remarks));
                            }
                        }
                    }
                }
            }

            this.gcgbPlasterCollectionList.RaiseListChangedEvents = true;
            this.gcgbPlasterCollectionList.ResetBindings();
        }



        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<GCGBPlasterCollection> inValidGCGBPlasterCollection = this.GCGBPlasterCollectionList.Where(gcgbPlasterCollection => !gcgbPlasterCollection.IsValid).ToList();
                return this.GCGBPlasterMaster.IsValid && inValidGCGBPlasterCollection.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.gcgbPlasterMaster.GCGBPlasterDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    if (!GlobalUserPermission.GetEditable("GCGBPlasterApproved", this.GCGBPlasterID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBPlasterEditable", this.GCGBPlasterID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.gcgbPlasterMaster.GCGBPlasterDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBPlasterVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBPlasterEditable", this.GCGBPlasterID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.gcgbPlasterMaster.GCGBPlasterDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBPlasterUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBPlasterEditable", this.GCGBPlasterID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int gcgbPlasterID)
        {
            this.ReadonlyMode = true;
            if (this.GCGBPlasterID == gcgbPlasterID) this.GCGBPlasterID = -1; //Enforce to reload
            this.GCGBPlasterID = gcgbPlasterID;
        }

        public void New()
        {
            this.ReadonlyMode = false;
            if (this.GCGBPlasterID == 0) this.GCGBPlasterID = -1;
            this.GCGBPlasterID = 0;
        }

        public void Edit()
        {
            this.ReadonlyMode = false;
            int gcgbPlasterID = this.GCGBPlasterID;

            this.GCGBPlasterID = -1;
            this.GCGBPlasterID = gcgbPlasterID;
        }

        #region Save & Delete Method


        public bool Save()
        {
            int gcgbPlasterID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref gcgbPlasterID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(gcgbPlasterID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    
                }

                this.Fill(gcgbPlasterID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.gcgbPlasterMaster.GCGBPlasterID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.gcgbPlasterMaster.GCGBPlasterID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.gcgbPlasterMaster.GCGBPlasterID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.GCGBPlasterMaster.PreparedEmployeeID, this.GCGBPlasterMaster.GCGBPlasterDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });


            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int gcgbPlasterID)
        {
            GCGBPlasterDTS.GCGBPlasterMasterDataTable masterDataTable;
            GCGBPlasterDTS.GCGBPlasterMasterRow masterRow;

            if (this.gcgbPlasterMaster.GCGBPlasterID <= 0) //Add
            {
                masterDataTable = new GCGBPlasterDTS.GCGBPlasterMasterDataTable();
                masterRow = masterDataTable.NewGCGBPlasterMasterRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(gcgbPlasterMaster.GCGBPlasterID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(gcgbPlasterMaster.GCGBPlasterID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.GCGBCommandID = this.gcgbPlasterMaster.GCGBCommandID;
            masterRow.GCGBCommandReference = this.gcgbPlasterMaster.GCGBCommandReference;
            masterRow.GCGBCommandDate = this.gcgbPlasterMaster.GCGBCommandDate;

            masterRow.GCGBPlasterDate = this.gcgbPlasterMaster.GCGBPlasterDate;
            masterRow.GCGBPlasterReference = this.gcgbPlasterMaster.GCGBPlasterReference;
            masterRow.EntryDate = this.gcgbPlasterMaster.EntryDate;

            masterRow.GCGBCommandTypeID = 1;

            masterRow.PreparedEmployeeID = this.gcgbPlasterMaster.PreparedEmployeeID;
            masterRow.ApprovedEmployeeID = this.gcgbPlasterMaster.ApprovedEmployeeID;

            masterRow.Description = this.gcgbPlasterMaster.Description;
            masterRow.Remarks = this.gcgbPlasterMaster.Remarks;

            masterRow.IsDraft = this.gcgbPlasterMaster.IsDraft;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            masterRow.Approved = false;

            if (this.gcgbPlasterMaster.GCGBPlasterID <= 0) masterDataTable.AddGCGBPlasterMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            gcgbPlasterID = masterRow.GCGBPlasterID;

            return rowsAffected == 1;

        }

        private bool SaveDetail(int gcgbPlasterID)
        {
            int serialID = 0; int rowsAffected = 0; int rowsPayment = 0;

            GCGBPlasterDTS.GCGBPlasterCollectionDataTable collectionDataTable = new GCGBPlasterDTS.GCGBPlasterCollectionDataTable();

            foreach (GCGBPlasterCollection gcgbPlasterCollection in this.gcgbPlasterCollectionList)
            {
                if (gcgbPlasterCollection.Quantity > 0)
                {
                    rowsPayment++;

                    GCGBPlasterDTS.GCGBPlasterCollectionRow collectionRow = collectionDataTable.NewGCGBPlasterCollectionRow();

                    collectionRow.GCGBPlasterID = gcgbPlasterID;
                    collectionRow.SerialID = ++serialID;

                    collectionRow.GCGBPlasterReference = this.gcgbPlasterMaster.GCGBPlasterReference;
                    collectionRow.GCGBPlasterDate = this.gcgbPlasterMaster.GCGBPlasterDate;

                    collectionRow.GCGBCommandID = gcgbPlasterCollection.GCGBCommandID;
                    collectionRow.GCGBCommandSerialID = gcgbPlasterCollection.GCGBCommandSerialID;

                    collectionRow.GCGBCommandTypeID = 1;

                    collectionRow.WorkerNameID = gcgbPlasterCollection.WorkerNameID;

                    collectionRow.MaterialSetID = gcgbPlasterCollection.MaterialSetID;                   

                    collectionRow.Quantity = gcgbPlasterCollection.Quantity;
                    collectionRow.QuantityLoss = gcgbPlasterCollection.QuantityLoss;

                    collectionRow.MaterialSemiID1 = gcgbPlasterCollection.MaterialSemiID1;
                    collectionRow.QuantityPerSet1 = gcgbPlasterCollection.QuantityPerSet1;
                    collectionRow.QuantitySemi1 = gcgbPlasterCollection.QuantitySemi1;
                    collectionRow.QuantitySemiBad1 = gcgbPlasterCollection.QuantitySemiBad1;
                    collectionRow.QuantitySemiLoss1 = gcgbPlasterCollection.QuantitySemiLoss1;
                    collectionRow.QuantitySemiPreviousMinus1 = gcgbPlasterCollection.QuantitySemiPreviousMinus1;

                    collectionRow.MaterialSemiID2 = gcgbPlasterCollection.MaterialSemiID2;
                    collectionRow.QuantityPerSet2 = gcgbPlasterCollection.QuantityPerSet2;
                    collectionRow.QuantitySemi2 = gcgbPlasterCollection.QuantitySemi2;
                    collectionRow.QuantitySemiBad2 = gcgbPlasterCollection.QuantitySemiBad2;
                    collectionRow.QuantitySemiLoss2 = gcgbPlasterCollection.QuantitySemiLoss2;
                    collectionRow.QuantitySemiPreviousMinus2 = gcgbPlasterCollection.QuantitySemiPreviousMinus2;

                    collectionRow.MaterialSemiID3 = gcgbPlasterCollection.MaterialSemiID3;                                       
                    collectionRow.QuantityPerSet3 = gcgbPlasterCollection.QuantityPerSet3;
                    collectionRow.QuantitySemi3 = gcgbPlasterCollection.QuantitySemi3;
                    collectionRow.QuantitySemiBad3 = gcgbPlasterCollection.QuantitySemiBad3;
                    collectionRow.QuantitySemiLoss3 = gcgbPlasterCollection.QuantitySemiLoss3;
                    collectionRow.QuantitySemiPreviousMinus3 = gcgbPlasterCollection.QuantitySemiPreviousMinus3;                 
                                          

                    collectionRow.Remarks = gcgbPlasterCollection.Remarks;

                    collectionRow.Approved = false;
                    collectionRow.InActive = false;

                    collectionDataTable.AddGCGBPlasterCollectionRow(collectionRow);
                }
            }

            rowsAffected = this.CollectionTableAdapter.Update(collectionDataTable);
            if (rowsAffected != rowsPayment) throw new System.ArgumentException("Insufficient save", "Save detail");


            int rowsUpdated = this.CollectionTableAdapter.GCGBPlasterSaveUpdate(gcgbPlasterID, (int)GlobalMsADO.UpdateOption.SaveUpdate);
            if (rowsUpdated > rowsPayment) throw new System.ArgumentException("Insufficient save", "Save update");


        #endregion <Item Category>


            return true;
        }


        private bool SaveUndo(int gcgbPlasterID)
        {
            int rowsUpdated = this.CollectionTableAdapter.GCGBPlasterSaveUpdate(gcgbPlasterID, (int)GlobalMsADO.UpdateOption.SaveUndo);
            int rowsDeleted = this.CollectionTableAdapter.Delete(gcgbPlasterID);

            if (rowsUpdated > rowsDeleted) throw new System.ArgumentException("Insufficient save", "Save undo");

            return true;
        }


        private bool SaveConflict()
        {
            return GlobalDataObjectAction.SaveConflict("GCGBPlasterSaveConflict", this.GCGBPlasterID);
        }



        #region Import Excel

        public bool ImportGCGBPlaster(string fileName)
        {
            try
            {
                if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict"); // || !this.IsDirty

                double paymentAmount = 0;
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.GCGBPlaster, fileName);
                if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                {
                    //Step 1: Check all rows to validate imported data
                    foreach (DataRow excelDataRow in excelDataTable.Rows)
                    {
                        if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                        {
                            if (paymentAmount > 0)
                            {
                                GCGBPlasterCollection gcgbPlasterCollection = this.GCGBPlasterCollectionList.FindItem(TypeDescriptor.CreateProperty(typeof(GCGBPlasterCollection), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                if (gcgbPlasterCollection == null || gcgbPlasterCollection.RemainsQuantity < Math.Round(gcgbPlasterCollection.Quantity + paymentAmount, GlobalVariables.Round0Quantity))
                                    exceptionTable.AddException(new string[] { gcgbPlasterCollection == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                            }
                        }
                        else
                            exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                    }

                    //Step 2: Start import
                    if (exceptionTable.Table.Rows.Count <= 0)
                    {
                        this.GCGBPlasterCollectionList.RaiseListChangedEvents = false;

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                            {
                                if (paymentAmount > 0)
                                {
                                    GCGBPlasterCollection gcgbPlasterCollection = this.GCGBPlasterCollectionList.FindItem(TypeDescriptor.CreateProperty(typeof(GCGBPlasterCollection), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                    if (gcgbPlasterCollection == null || gcgbPlasterCollection.RemainsQuantity < Math.Round(gcgbPlasterCollection.Quantity + paymentAmount, GlobalVariables.Round0Quantity))
                                        exceptionTable.AddException(new string[] { gcgbPlasterCollection == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                                    else
                                    {
                                        gcgbPlasterCollection.Quantity = gcgbPlasterCollection.Quantity + paymentAmount;
                                        gcgbPlasterCollection.Remarks = excelDataRow["Remarks"].ToString();
                                    }
                                }
                            }
                            else
                                exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                        }

                        this.GCGBPlasterCollectionList.RaiseListChangedEvents = true;
                        this.GCGBPlasterCollectionList.ResetBindings();
                    }

                    if (exceptionTable.Table.Rows.Count > 0)
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);
                }
                return true;
            }
            catch (System.Exception exception)
            {
                this.GCGBPlasterCollectionList.RaiseListChangedEvents = true;
                throw exception;
            }
        }

        #endregion Import Excel



        private void RestoreProcedure()
        {

            string queryString;
            string[] queryArray;


            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          LEFT(DATENAME(Month, GCGBPlasterMaster.GCGBPlasterDate), 3) + ', ' + DATENAME(Year, GCGBPlasterMaster.GCGBPlasterDate) AS Month, * " + "\r\n";
            queryString = queryString + "       FROM            GCGBPlasterMaster " + "\r\n";
            queryString = queryString + "       WHERE           GCGBPlasterMaster.GCGBPlasterDate >= @LowerFillterDate AND GCGBPlasterMaster.GCGBPlasterDate <= @UpperFillterDate " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterListing", queryString);



            queryString = " @GCGBPlasterID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPlasterMaster.GCGBPlasterID, GCGBPlasterMaster.GCGBPlasterDate, GCGBPlasterMaster.GCGBPlasterReference, GCGBPlasterMaster.GCGBCommandID, GCGBCommandMaster.GCGBCommandReference, GCGBCommandMaster.GCGBCommandDate, GCGBPlasterMaster.GCGBCommandTypeID, GCGBPlasterMaster.EntryDate, GCGBPlasterMaster.PreparedEmployeeID, GCGBPlasterMaster.ApprovedEmployeeID, GCGBPlasterMaster.Description, GCGBPlasterMaster.Remarks, GCGBPlasterMaster.UserID, GCGBPlasterMaster.UserOrganizationID, GCGBPlasterMaster.Approved, GCGBPlasterMaster.IsDraft " + "\r\n";
            queryString = queryString + "       FROM            GCGBPlasterMaster INNER JOIN GCGBCommandMaster ON GCGBPlasterMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID " + "\r\n";
            queryString = queryString + "       WHERE           GCGBPlasterID = @GCGBPlasterID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterMasterSelect", queryString);


            queryString = " @GCGBPlasterDate datetime, @GCGBPlasterReference nvarchar(50), @GCGBCommandID int, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Description nvarchar(150), @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit, @IsDraft bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBPlasterMaster (GCGBPlasterDate, GCGBPlasterReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved, IsDraft) VALUES (@GCGBPlasterDate, @GCGBPlasterReference, @GCGBCommandID, @GCGBCommandTypeID, @EntryDate, @PreparedEmployeeID, @ApprovedEmployeeID, @Description, @Remarks, @UserID, @UserOrganizationID, @Approved, @IsDraft) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPlasterID, GCGBPlasterDate, GCGBPlasterReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved, IsDraft FROM GCGBPlasterMaster WHERE (GCGBPlasterID = SCOPE_IDENTITY()) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterMasterInsert", queryString);


            queryString = " @GCGBPlasterID int, @GCGBPlasterDate datetime, @GCGBPlasterReference nvarchar(50), @GCGBCommandID int, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Description nvarchar(150), @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit, @IsDraft bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBPlasterMaster SET GCGBPlasterDate = @GCGBPlasterDate, GCGBPlasterReference = @GCGBPlasterReference, GCGBCommandID = @GCGBCommandID, GCGBCommandTypeID = @GCGBCommandTypeID, EntryDate = @EntryDate, PreparedEmployeeID = @PreparedEmployeeID, ApprovedEmployeeID = @ApprovedEmployeeID, Description = @Description, Remarks = @Remarks, UserID = @UserID, UserOrganizationID = @UserOrganizationID, Approved = @Approved, IsDraft = @IsDraft WHERE GCGBPlasterID = @GCGBPlasterID " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPlasterID, GCGBPlasterDate, GCGBPlasterReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved, IsDraft FROM GCGBPlasterMaster WHERE (GCGBPlasterID = @GCGBPlasterID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterMasterUpdate", queryString);


            queryString = " @GCGBPlasterID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM     GCGBPlasterMaster WHERE GCGBPlasterID = @GCGBPlasterID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterMasterDelete", queryString);




            queryString = " @GCGBPlasterID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPlasterID, SerialID, GCGBPlasterReference, GCGBPlasterDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive " + "\r\n";
            queryString = queryString + "       FROM            GCGBPlasterCollection " + "\r\n";
            queryString = queryString + "       WHERE           GCGBPlasterID = @GCGBPlasterID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterCollectionSelect", queryString);


            queryString = " @GCGBPlasterID int, @SerialID int, @GCGBPlasterReference nvarchar(50), @GCGBPlasterDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @GCGBCommandTypeID int, @WorkerNameID int, @MaterialSetID int, @Quantity float, @QuantityLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiBad1 float, @QuantitySemiLoss1 float, @QuantitySemiPreviousMinus1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiBad2 float, @QuantitySemiLoss2 float, @QuantitySemiPreviousMinus2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiBad3 float, @QuantitySemiLoss3 float, @QuantitySemiPreviousMinus3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBPlasterCollection (GCGBPlasterID, SerialID, GCGBPlasterReference, GCGBPlasterDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1,  QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2,  QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3,  QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive) " + "\r\n";
            queryString = queryString + "                       VALUES (@GCGBPlasterID, @SerialID, @GCGBPlasterReference, @GCGBPlasterDate, @GCGBCommandID, @GCGBCommandSerialID, @GCGBCommandTypeID, @WorkerNameID, @MaterialSetID, @Quantity, @QuantityLoss, @MaterialSemiID1, @QuantityPerSet1, @QuantitySemi1, @QuantitySemiBad1, @QuantitySemiLoss1, @QuantitySemiPreviousMinus1, @MaterialSemiID2, @QuantityPerSet2, @QuantitySemi2, @QuantitySemiBad2, @QuantitySemiLoss2, @QuantitySemiPreviousMinus2, @MaterialSemiID3, @QuantityPerSet3, @QuantitySemi3, @QuantitySemiBad3, @QuantitySemiLoss3, @QuantitySemiPreviousMinus3, @Remarks, @Approved, @InActive) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPlasterID, SerialID, GCGBPlasterReference, GCGBPlasterDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive FROM GCGBPlasterCollection WHERE (GCGBPlasterID = @GCGBPlasterID) AND (SerialID = @SerialID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterCollectionInsert", queryString);


            queryString = " @GCGBPlasterID int, @SerialID int, @GCGBPlasterReference nvarchar(50), @GCGBPlasterDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @GCGBCommandTypeID int, @WorkerNameID int, @MaterialSetID int, @Quantity float, @QuantityLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiBad1 float, @QuantitySemiLoss1 float, @QuantitySemiPreviousMinus1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiBad2 float, @QuantitySemiLoss2 float, @QuantitySemiPreviousMinus2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiBad3 float, @QuantitySemiLoss3 float, @QuantitySemiPreviousMinus3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBPlasterCollection SET GCGBPlasterReference = @GCGBPlasterReference, GCGBPlasterDate = @GCGBPlasterDate, GCGBCommandID = @GCGBCommandID, GCGBCommandSerialID = @GCGBCommandSerialID, GCGBCommandTypeID = @GCGBCommandTypeID, WorkerNameID = @WorkerNameID, MaterialSetID = @MaterialSetID, Quantity = @Quantity, QuantityLoss = @QuantityLoss, MaterialSemiID1 = @MaterialSemiID1, QuantityPerSet1 = @QuantityPerSet1, QuantitySemi1 = @QuantitySemi1, QuantitySemiBad1 = @QuantitySemiBad1,  QuantitySemiLoss1 = @QuantitySemiLoss1, QuantitySemiPreviousMinus1 = @QuantitySemiPreviousMinus1, " + "\r\n";
            queryString = queryString + "                       MaterialSemiID2 = @MaterialSemiID2, QuantityPerSet2 = @QuantityPerSet2, QuantitySemi2 = @QuantitySemi2, QuantitySemiBad2 = @QuantitySemiBad2, QuantitySemiLoss2 = @QuantitySemiLoss2, QuantitySemiPreviousMinus2 = @QuantitySemiPreviousMinus2, MaterialSemiID3 = @MaterialSemiID3, QuantityPerSet3 = @QuantityPerSet3, QuantitySemi3 = @QuantitySemi3, QuantitySemiBad3 = @QuantitySemiBad3, QuantitySemiLoss3 = @QuantitySemiLoss3, QuantitySemiPreviousMinus3 = @QuantitySemiPreviousMinus3, Remarks = @Remarks, Approved = @Approved, InActive = @InActive WHERE (GCGBPlasterID = @GCGBPlasterID) AND (SerialID = @SerialID) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBPlasterID, SerialID, GCGBPlasterReference, GCGBPlasterDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID, MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive FROM GCGBPlasterCollection WHERE (GCGBPlasterID = @GCGBPlasterID) AND (SerialID = @SerialID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterCollectionUpdate", queryString);            


            queryString = " @GCGBPlasterID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM     GCGBPlasterCollection WHERE GCGBPlasterID = @GCGBPlasterID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterCollectionDelete", queryString);


            string queryEdit; string queryNew;

            queryEdit = "               SELECT          GCGBCommandCollection.GCGBCommandID, GCGBCommandCollection.SerialID AS GCGBCommandSerialID, GCGBCommandCollection.GCGBCommandReference, GCGBCommandCollection.GCGBCommandDate, GCGBPlasterCollection.WorkerNameID, GCGBCommandCollection.MaterialSetID, ROUND(GCGBCommandCollection.Quantity *  @IsDraft + GCGBCommandCollection.QuantityWHOutput * (1 - @IsDraft) - GCGBCommandCollection.QuantityPlaster - GCGBCommandCollection.QuantityPlasterLoss + GCGBPlasterCollection.Quantity + GCGBPlasterCollection.QuantityLoss, " + GlobalVariables.Round0Amount.ToString() + ") AS RemainsQuantity, GCGBPlasterCollection.Quantity, GCGBPlasterCollection.QuantityLoss, " + "\r\n";
            queryEdit = queryEdit + "                   GCGBCommandCollection.MaterialSemiID1, GCGBCommandCollection.QuantityPerSet1, GCGBPlasterCollection.QuantitySemiBad1, GCGBPlasterCollection.QuantitySemiLoss1, GCGBPlasterCollection.QuantitySemiPreviousMinus1, GCGBCommandCollection.MaterialSemiID2, GCGBCommandCollection.QuantityPerSet2, GCGBPlasterCollection.QuantitySemiBad2, GCGBPlasterCollection.QuantitySemiLoss2, GCGBPlasterCollection.QuantitySemiPreviousMinus2, GCGBCommandCollection.MaterialSemiID3, GCGBCommandCollection.QuantityPerSet3, GCGBPlasterCollection.QuantitySemiBad3, GCGBPlasterCollection.QuantitySemiLoss3, GCGBPlasterCollection.QuantitySemiPreviousMinus3, GCGBPlasterCollection.Remarks, 0 AS RSC " + "\r\n";
            queryEdit = queryEdit + "   FROM            GCGBCommandCollection INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   GCGBPlasterCollection ON GCGBCommandCollection.GCGBCommandID = GCGBPlasterCollection.GCGBCommandID AND GCGBCommandCollection.SerialID = GCGBPlasterCollection.GCGBCommandSerialID " + "\r\n";
            queryEdit = queryEdit + "   WHERE           (@GCGBCommandID <= 0 OR @GCGBCommandID = GCGBCommandCollection.GCGBCommandID) AND GCGBPlasterCollection.GCGBPlasterID = @GCGBPlasterID " + "\r\n";

            queryNew = "                SELECT          GCGBCommandCollection.GCGBCommandID, GCGBCommandCollection.SerialID AS GCGBCommandSerialID, GCGBCommandCollection.GCGBCommandReference, GCGBCommandCollection.GCGBCommandDate, 0 AS WorkerNameID, GCGBCommandCollection.MaterialSetID, ROUND(GCGBCommandCollection.Quantity * @IsDraft + GCGBCommandCollection.QuantityWHOutput * (1 - @IsDraft) - GCGBCommandCollection.QuantityPlaster - GCGBCommandCollection.QuantityPlasterLoss, " + GlobalVariables.Round0Amount.ToString() + ") AS RemainsQuantity, 0 AS Quantity, 0 AS QuantityLoss, " + "\r\n";
            queryNew = queryNew + "                     GCGBCommandCollection.MaterialSemiID1, GCGBCommandCollection.QuantityPerSet1, 0 AS QuantitySemiBad1, 0 AS QuantitySemiLoss1, 0 AS QuantitySemiPreviousMinus1, GCGBCommandCollection.MaterialSemiID2, GCGBCommandCollection.QuantityPerSet2, 0 AS QuantitySemiBad2, 0 AS QuantitySemiLoss2, 0 AS QuantitySemiPreviousMinus2, GCGBCommandCollection.MaterialSemiID3, GCGBCommandCollection.QuantityPerSet3, 0 AS QuantitySemiBad3, 0 AS QuantitySemiLoss3, 0 AS QuantitySemiPreviousMinus3, '' AS Remarks, 1 AS RSC " + "\r\n";
            queryNew = queryNew + "     FROM            GCGBCommandCollection " + "\r\n";
            queryNew = queryNew + "     WHERE           (@GCGBCommandID <= 0 OR @GCGBCommandID = GCGBCommandCollection.GCGBCommandID) AND GCGBCommandCollection.Approved = 1 AND ROUND(GCGBCommandCollection.Quantity * @IsDraft + GCGBCommandCollection.QuantityWHOutput * (1 - @IsDraft) - GCGBCommandCollection.QuantityPlaster - GCGBCommandCollection.QuantityPlasterLoss, " + GlobalVariables.Round0Amount.ToString() + ") > 0 " + "\r\n";

            queryString = " @GCGBPlasterID Int, @GCGBCommandID Int, @ReadonlyMode bit, @IsDraft bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + " IF (@GCGBPlasterID <= 0) " + "\r\n";
            queryString = queryString + "    BEGIN " + "\r\n";
            queryString = queryString + "       " + queryNew + "\r\n";
            queryString = queryString + "        ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "    END " + "\r\n";
            queryString = queryString + " ELSE " + "\r\n";

            queryString = queryString + "   IF (@ReadonlyMode = 1) " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";
            queryString = queryString + "   ELSE " + "\r\n"; //FULL SELECT FOR EDIT MODE

            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           UNION ALL " + "\r\n";
            queryString = queryString + "           " + queryNew + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID, RSC " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBPlasterGetDetail", queryString);           



            queryString = " @GCGBPlasterID int, @UpdateOption int " + "\r\n"; //UpdateOption: 1 Savedetail, -1:Saveundo
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBCommandCollection " + "\r\n";
            queryString = queryString + "       SET             GCGBCommandCollection.QuantityPlaster = ROUND(GCGBCommandCollection.QuantityPlaster + GCGBPlasterCollection.Quantity * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantityPlasterLoss = ROUND(GCGBCommandCollection.QuantityPlasterLoss + GCGBPlasterCollection.QuantityLoss * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), " + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection.QuantitySemiPlasterBad1 = ROUND(GCGBCommandCollection.QuantitySemiPlasterBad1 + GCGBPlasterCollection.QuantitySemiBad1 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiPlasterLoss1 = ROUND(GCGBCommandCollection.QuantitySemiPlasterLoss1 + GCGBPlasterCollection.QuantitySemiLoss1 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiPlasterPreviousMinus1 = ROUND(GCGBCommandCollection.QuantitySemiPlasterPreviousMinus1 + GCGBPlasterCollection.QuantitySemiPreviousMinus1 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), " + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection.QuantitySemiPlasterBad2 = ROUND(GCGBCommandCollection.QuantitySemiPlasterBad2 + GCGBPlasterCollection.QuantitySemiBad2 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiPlasterLoss2 = ROUND(GCGBCommandCollection.QuantitySemiPlasterLoss2 + GCGBPlasterCollection.QuantitySemiLoss2 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiPlasterPreviousMinus2 = ROUND(GCGBCommandCollection.QuantitySemiPlasterPreviousMinus2 + GCGBPlasterCollection.QuantitySemiPreviousMinus2 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + ")," + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection.QuantitySemiPlasterBad3 = ROUND(GCGBCommandCollection.QuantitySemiPlasterBad3 + GCGBPlasterCollection.QuantitySemiBad3 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiPlasterLoss3 = ROUND(GCGBCommandCollection.QuantitySemiPlasterLoss3 + GCGBPlasterCollection.QuantitySemiLoss3 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiPlasterPreviousMinus3 = ROUND(GCGBCommandCollection.QuantitySemiPlasterPreviousMinus3 + GCGBPlasterCollection.QuantitySemiPreviousMinus3 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + ")" + "\r\n";
            queryString = queryString + "       FROM           (SELECT GCGBCommandID, GCGBCommandSerialID, SUM(Quantity) AS Quantity, SUM(QuantityLoss) As QuantityLoss, SUM(QuantitySemiBad1) AS QuantitySemiBad1, SUM(QuantitySemiLoss1) AS QuantitySemiLoss1, SUM(QuantitySemiPreviousMinus1) AS QuantitySemiPreviousMinus1, SUM(QuantitySemiBad2) AS QuantitySemiBad2, SUM(QuantitySemiLoss2) AS QuantitySemiLoss2, SUM(QuantitySemiPreviousMinus2) AS QuantitySemiPreviousMinus2, SUM(QuantitySemiBad3) AS QuantitySemiBad3, SUM(QuantitySemiLoss3) AS QuantitySemiLoss3, SUM(QuantitySemiPreviousMinus3) AS QuantitySemiPreviousMinus3  FROM GCGBPlasterCollection WHERE GCGBPlasterID = @GCGBPlasterID GROUP BY GCGBCommandID, GCGBCommandSerialID) GCGBPlasterCollection INNER JOIN " + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection ON GCGBPlasterCollection.GCGBCommandID = GCGBCommandCollection.GCGBCommandID AND GCGBPlasterCollection.GCGBCommandSerialID = GCGBCommandCollection.SerialID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBPlasterSaveUpdate", queryString);




            /// <summary>
            /// Check for save conflict
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1  GCGBPlasterMaster.GCGBPlasterID FROM GCGBPlasterMaster INNER JOIN GCGBCommandMaster ON GCGBPlasterMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID AND GCGBPlasterMaster.GCGBPlasterDate < GCGBCommandMaster.GCGBCommandDate ";
            //queryArray[1] = " SELECT TOP 1  GCGBCommandID FROM GCGBCommandCollection WHERE (ROUND(Quantity * @IsDraft  + QuantityWHOutput * (1 - @IsDraft) - QuantityPlaster - QuantityPlasterLoss, " + GlobalVariables.Round0Amount.ToString() + ") < 0) ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBPlasterSaveConflict", queryArray);

            

            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1 GCGBPlasterID FROM GCGBPlasterMaster WHERE GCGBPlasterID = @FindIdentityID AND Approved = 1 ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBPlasterApproved", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[0];

            SQLDatabase.CreateProcedureToCheckExisting("GCGBPlasterEditable");// queryArray);

            
        }

    
     
    }



    public class MyBindingList<T> : BindingList<T>
    {
        protected override int FindCore(PropertyDescriptor property, object key)
        {

            // Specify search columns
            if (property == null) return -1;

            // Get list to search
            List<T> items = this.Items as List<T>;

            // Traverse list for value
            foreach (T item in items)
            {

                // Test column search value
                string value = (string)property.GetValue(item);

                // If value is the search value, return the 
                // index of the data item
                if ((string)key == value) return IndexOf(item);
            }
            return -1;
        }

        public T FindItem(PropertyDescriptor property, object key)
        {

            // Specify search columns
            if (property == null) return default(T);

            // Get list to search
            List<T> items = this.Items as List<T>;

            // Traverse list for value
            foreach (T item in items)
            {

                // Test column search value
                string value = (string)property.GetValue(item);

                // If value is the search value, return the 
                // index of the data item
                if ((string)key == value) return item;
            }
            return default(T);
        }
    }



}

